﻿using CPAIS.Model;
using CPAIS.Updater;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CPAIS.DbAccessor
{
    public class DbAccessorFactory
    {
        /// <summary>
        /// Gets and sets database accessor.
        /// </summary>
        public IDbAccessor DbAccessor
        {
            get;
            set;
        }

        /// <summary>
        /// Default constructor.
        /// </summary>
        public DbAccessorFactory()
        {
            DbAccessor = null;
        }

        /// <summary>
        /// Constructs an instance of DbAccessorFactory.
        /// </summary>
        /// <param name="DbAccessor"></param>
        public DbAccessorFactory(IDbAccessor DbAccessor)
        {
            this.DbAccessor = DbAccessor;
        }

        /// <summary>
        /// Loads log model from database.
        /// </summary>
        /// <returns></returns>
        public LogModel LoadLog(string logName, StateUpdater updater)
        {
            if (updater != null)
                updater.Begin();

            try
            {
                LogModel logModel = new LogModel(logName);

                if (DbAccessor == null)
                    throw new Exception("Database is not established");

                using (DbConnection conn = DbAccessor.CreateConnection())
                {
                    conn.Open();
                    DbDataReader reader = null;
                    DbDataAdapter adapter = null;
                    DbCommand cmd = DbAccessor.CreateCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = string.Format("SELECT ID FROM log WHERE Valid={0} AND Name='{1}'", 1, logName);
                    reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        int logID = reader.GetInt32(0);
                        reader.Close();
                        cmd.CommandText = string.Format("SELECT ID, PID, Sex, Cost, Department, Birthday, Admit, Discharge FROM trace WHERE LogID={0}", logID);
                        adapter = DbAccessor.CreateDataAdapter(cmd);
                        DataTable traceTable = new DataTable();
                        adapter.Fill(traceTable);
                        var traceRows = traceTable.Rows;
                        foreach (DataRow traceRow in traceRows)
                        {
                            int traceID = (int)traceRow[0];
                            Trace trace = new Trace(logModel, traceRow[1] as string);
                            string sex = traceRow[2] as string;
                            trace.Sex = sex == SexType.Male.ToString() ? SexType.Male : (sex == SexType.Female.ToString() ? SexType.Female : SexType.Unknown);
                            trace.Cost = (double)traceRow[3];
                            trace.Department = traceRow[4] as string;
                            trace.Birthday = (DateTime)traceRow[5];
                            trace.Admit = (DateTime)traceRow[6];
                            trace.Discharge = (DateTime)traceRow[7];

                            cmd.CommandText = string.Format("SELECT Name, Day, Timestamp, TimestampAvailable FROM action WHERE TraceID={0}", traceID);
                            DataTable actionTable = new DataTable();
                            adapter = DbAccessor.CreateDataAdapter(cmd);
                            adapter.Fill(actionTable);
                            var actionRows = actionTable.Rows;
                            foreach (DataRow actionRow in actionRows)
                            {
                                ActionEvent action = null;
                                bool tsAvailable = (bool)actionRow[3];
                                if (tsAvailable)
                                    action = new ActionEvent(trace, (DateTime)actionRow[2], actionRow[0] as string);
                                else
                                    action = new ActionEvent(trace, (int)actionRow[1], actionRow[0] as string);
                                trace.AddAction(action);
                            }

                            cmd.CommandText = string.Format("SELECT Name, Day, Timestamp, TimestampAvailable FROM observation WHERE TraceID={0}", traceID);
                            DataTable observationTable = new DataTable();
                            adapter = DbAccessor.CreateDataAdapter(cmd);
                            adapter.Fill(observationTable);
                            var observationRows = observationTable.Rows;
                            foreach (DataRow observationRow in observationRows)
                            {
                                ObservationEvent observation = null;
                                bool tsAvailable = (bool)observationRow[3];
                                if (tsAvailable)
                                    observation = new ObservationEvent(trace, (DateTime)observationRow[2], observationRow[0] as string);
                                else
                                    observation = new ObservationEvent(trace, (int)observationRow[1], observationRow[0] as string);
                                trace.AddObservation(observation);
                            }

                            cmd.CommandText = string.Format("SELECT Name FROM diagnosis WHERE TraceID={0}", traceID);
                            DataTable diagnosisTable = new DataTable();
                            adapter = DbAccessor.CreateDataAdapter(cmd);
                            adapter.Fill(diagnosisTable);
                            var diagnosisRows = diagnosisTable.Rows;
                            foreach (DataRow diagnosisRow in diagnosisRows)
                            {
                                trace.AddDiagnosis(diagnosisRow[0] as string);
                            }
                            trace.OrderTrace();
                            logModel.AddTrace(trace);
                            if (updater != null)
                                updater.UpdateMessage(string.Format("{0} traces have been loaded...", logModel.TraceCount));
                        }
                    }
                    else
                        reader.Close();
                }

                logModel.Valid = true;
                if (updater != null)
                    updater.Complete();
                return logModel;
            }
            catch (Exception ex)
            {
                if (updater != null)
                {
                    if (updater.State != UpdateState.Cancelled)
                        updater.Fail(ex.Message);
                    return null;
                }
                else
                    throw ex;
            }
        }

        /// <summary>
        /// Saves log model to database.
        /// </summary>
        public void SaveLog(LogModel logModel, ProgressUpdater updater)
        {
            if (updater != null)
                updater.Begin();

            try
            {
                if (!logModel.Valid)
                    throw new InvalidOperationException("The log model to insert into database is invalid");

                if (DbAccessor == null)
                    throw new Exception("Database is not established");

                using (DbConnection conn = DbAccessor.CreateConnection())
                {
                    conn.Open();
                    DbCommand cmd = DbAccessor.CreateCommand();
                    cmd.Connection = conn;
                    DbDataReader reader = null;
                    int logID;

                    const string DateTimeFormat = "yyyy-MM-dd hh:mm:ss";
                    const string DateFormat = "yyyy-MM-dd";

                    #region Check if it has been existed
                    cmd.CommandText = string.Format("SELECT ID, Valid FROM log WHERE Name='{0}'", logModel.LogName);
                    reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        bool valid = reader.GetBoolean(1);
                        if (valid)
                            return;
                        logID = reader.GetInt32(0);
                        reader.Close();
                        cmd.CommandText = string.Format("DELETE FROM log WHERE ID={0}", logID);
                        cmd.ExecuteNonQuery();
                    }
                    else
                        reader.Close();
                    #endregion

                    DbTransaction trans = conn.BeginTransaction();
                    cmd.Transaction = trans;

                    try
                    {
                        cmd.CommandText = string.Format("INSERT INTO log (Name, Valid) VALUES ('{0}', 0)", logModel.LogName);
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "SELECT @@IDENTITY";
                        reader = cmd.ExecuteReader();
                        reader.Read();
                        logID = reader.GetInt32(0);
                        reader.Close();

                        var traces = logModel.Traces;
                        int traceLength = traces.Count;
                        for (int i = 0; i < traceLength; i++)
                        {
                            var trace = traces[i];
                            cmd.CommandText = string.Format("INSERT INTO trace (PID, Sex, Department, Cost, Birthday, Admit, Discharge, LogID) VALUES ('{0}', '{1}', '{2}', {3}, '{4}', '{5}', '{6}', {7})",
                                trace.ID, trace.Sex.ToString(), trace.Department, trace.Cost, trace.Birthday.ToString(DateFormat),
                                trace.Admit.ToString(DateTimeFormat), trace.Discharge.ToString(DateTimeFormat), logID);
                            cmd.ExecuteNonQuery();                            
                            cmd.CommandText = "SELECT @@IDENTITY";
                            reader = cmd.ExecuteReader();
                            int traceID = 0;
                            if (reader.Read())
                            {
                               traceID = reader.GetInt32(0);
                            }
                            reader.Close();

                            StringBuilder actionBuilder = new StringBuilder("INSERT INTO action (Name, Day, Timestamp, TimestampAvailable, TraceID) VALUES ");
                            bool first = true;
                            foreach (var action in trace.Actions)
                            {
                                if (first)
                                    first = false;
                                else
                                    actionBuilder.Append(", ");
                                actionBuilder.Append(string.Format("('{0}', {1}, '{2}', {3}, {4})",
                                    action.TypeName, action.Day,
                                    action.Timestamp.ToString(DateTimeFormat),
                                    action.IsTimestampAvailable ? 1 : 0, traceID));
                            }
                            if (trace.Actions.Count != 0)
                                cmd.CommandText = actionBuilder.ToString();
                            cmd.ExecuteNonQuery();

                            StringBuilder observationBuilder = new StringBuilder("INSERT INTO observation (Name, Day, Timestamp, TimestampAvailable, TraceID) VALUES ");
                            first = true;
                            foreach (var observation in trace.Observations)
                            {
                                if (first)
                                    first = false;
                                else
                                    observationBuilder.Append(", ");
                                observationBuilder.Append(string.Format("('{0}', {1}, '{2}', {3}, {4})",
                                    observation.TypeName, observation.Day,
                                    observation.Timestamp.ToString(DateTimeFormat),
                                    observation.IsTimestampAvailable ? 1 : 0, traceID));
                            }
                            if (trace.Observations.Count != 0)
                                cmd.CommandText = observationBuilder.ToString();
                            cmd.ExecuteNonQuery();

                            StringBuilder diagnosisBuilder = new StringBuilder("INSERT INTO diagnosis (Name, TraceID) VALUES ");
                            first = true;
                            var diagnoses = trace.Diagnoses;
                            foreach (var diagnosis in diagnoses)
                            {
                                if (first)
                                    first = false;
                                else
                                    diagnosisBuilder.Append(", ");
                                diagnosisBuilder.Append(string.Format("('{0}', {1})",
                                    diagnosis, traceID));
                            }
                            if (diagnoses.Length != 0)
                                cmd.CommandText = diagnosisBuilder.ToString();
                            cmd.ExecuteNonQuery();

                            if (updater != null)
                                updater.UpdateProgress((double)(i + 1) / traceLength);
                        }

                        cmd.CommandText = string.Format("UPDATE log SET Valid=1 WHERE ID={0}", logID);
                        cmd.ExecuteNonQuery();

                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw ex;
                    }
                }
                if (updater != null)
                    updater.Complete();
            }
            catch (Exception ex)
            {
                if (updater != null)
                {
                    if (updater.State == UpdateState.Cancelled)
                        return;
                    else
                        updater.Fail(ex.Message);
                }
                else
                    throw ex;
            }
        }
    }
}